package com.kcs.fxmargin.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.model.TDealsSum;

public class DealsSumDao {

	
	public TDealsSum search(Connection conn, PreparedStatement pstm, ResultSet rs, String cifNo) throws Exception {
		
		TDealsSum dealsSum	= new TDealsSum();
		StringBuffer sql 	= new StringBuffer();
		
		sql.append(" SELECT top 1 id, ");
		sql.append(" 	as_of_date, ");
		sql.append(" 	cif_no, ");
		sql.append(" 	cust_limit, ");
		sql.append(" 	cash_bal, ");
		sql.append(" 	realized_pl, ");
		sql.append(" 	unrealized_pl, ");
		sql.append(" 	equity_bal, ");
		sql.append(" 	initial_margin , ");
		sql.append(" 	excess_amt, ");
		sql.append(" 	mtn_margin, ");
		sql.append(" 	margin_call, ");
		sql.append(" 	margin_limit, ");
		sql.append(" 	active_status, ");
		sql.append(" 	create_by, ");
		sql.append(" 	create_dt, ");
		sql.append(" 	update_by, ");
		sql.append(" 	update_dt , ");
		sql.append(" 	row_version ");
		sql.append(" FROM t_deals_sum ");
		sql.append(" WHERE cif_no = ? ");
		sql.append(" 	AND active_status='A' ");
		sql.append(" ORDER BY as_of_date DESC ");
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, cifNo);
		
		rs = pstm.executeQuery();
		
		while(rs.next()){
			dealsSum.setId(rs.getString("id"));
			dealsSum.setAsOfDate(rs.getDate("as_of_date"));
			dealsSum.setCifNo(rs.getString("cif_no"));
			dealsSum.setCustLimit(rs.getBigDecimal("cust_limit"));
			dealsSum.setCashBal(rs.getBigDecimal("cash_bal"));
			dealsSum.setRealizedPl(rs.getBigDecimal("realized_pl"));
			dealsSum.setUnrealizedPl(rs.getBigDecimal("unrealized_pl"));
			dealsSum.setEquityBal(rs.getBigDecimal("equity_bal"));
			dealsSum.setInitialMargin(rs.getBigDecimal("initial_margin"));
			dealsSum.setExcessAmt(rs.getBigDecimal("excess_amt"));
			dealsSum.setMtnMargin(rs.getBigDecimal("mtn_margin"));
			dealsSum.setMarginCall(rs.getBigDecimal("margin_call"));
			dealsSum.setMarginLimit(rs.getBigDecimal("margin_limit"));
			dealsSum.setActiveStatus(rs.getString("active_status"));
			dealsSum.setCreateby(rs.getString("create_by"));
			dealsSum.setCreateDate(rs.getDate("create_dt"));
			dealsSum.setUpdateBy(rs.getString("update_by"));
			dealsSum.setUpdateDate(rs.getDate("update_dt"));
			dealsSum.setRowVersion(rs.getInt("row_version"));
		}
		
		return dealsSum;
	}
	
	public BigDecimal getCashBalance(Connection conn, 
			PreparedStatement pstm, 
			ResultSet rs, 
			String cifNo,
			Date date) throws Exception {
		
		BigDecimal cashBalance 	= new BigDecimal(0);
		StringBuffer sql 		= new StringBuffer();
		
		sql.append(" WITH param AS ");
		sql.append("   (SELECT ? cust_info_id, ");
		sql.append("             ? as_of_date) ");
		sql.append(" SELECT ( ");
		sql.append(" SELECT ISNULL( ");
		sql.append("                 ( SELECT pos.outstd_value + pos.accrued_interest ");
		sql.append("                  FROM t_acc_pos pos, param p ");
		sql.append("                  WHERE pos.active_status='A' ");
		sql.append("                    AND CAST(pos.as_of_date AS DATE) = p.as_of_date ");
		sql.append("                    AND pos.margin_acc_no = ");
		sql.append("                      (SELECT margin_acc_no ");
		sql.append("                       FROM m_cust_info ");
		sql.append("                       WHERE id=p.cust_info_id) ),0) + ");
		sql.append("   ( SELECT ISNULL(sum(a.amount),0) ");
		sql.append("    FROM t_acc_online a, ");
		sql.append("         param p ");
		sql.append("    WHERE a.active_status='A' ");
		sql.append("      AND a.cust_info_id = p.cust_info_id ");
		sql.append("      AND a.as_of_date = p.as_of_date ");
		sql.append("      AND a.type='D' ");
		sql.append("      AND NOT EXISTS ");
		sql.append("        ( SELECT 1 ");
		sql.append("         FROM t_acc_tran t, ");
		sql.append("              param p ");
		sql.append("         WHERE t.active_status='A' ");
		sql.append("           AND CAST(t.seq AS VARCHAR) = a.ref_no ");
		sql.append("           AND CAST(t.trade_date AS DATE) = p.as_of_date ");
		sql.append("           AND t.type='D' ) ) - ( ");
		sql.append(" SELECT ISNULL(sum(b.amount), 0) ");
		sql.append(" FROM t_acc_online b, ");
		sql.append("      param p ");
		sql.append(" WHERE b.active_status='A' ");
		sql.append("   AND b.cust_info_id = p.cust_info_id ");
		sql.append("   AND (p.as_of_date BETWEEN b.as_of_date AND b.value_date) ");
		sql.append("   AND b.type='W') ");
		sql.append(" ) as cash_balance ");
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, cifNo);
		pstm.setDate(2, commonUtils.convertUtilDatetoSQLDate(date));
		
		rs = pstm.executeQuery();
		
		while(rs.next()){
			cashBalance = rs.getBigDecimal("cash_balance");
		}
		
		System.out.println("Cash Balance = " + cashBalance);
		
		return cashBalance;
	}
}
